﻿
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading.Tasks;
using BCCommon;
using BCEntity.TMS.Fence;
using BCCommon.Enums.TMS;
using BCEntity.TMS.SpecificServiceType;
using StackExchange.Redis;

namespace BCData.TMS.Fence
{
    public class FenceData : IFenceData
    {
        private readonly IDatabaseContext database;
        private readonly string QUERY = @"SELECT
		    FenceId,
            MongoId,
		    FenceName,
		    FenceNumber,
		    CtrlCompanyId,
		    CtrlCompanyName,
		    WorkCompanyId,
		    WorkCompanyName,
		    ProvinceCode,
		    ProvinceName,
		    CityCode,
		    CityName,
		    CountyCode,
		    CountyName,
		    CreateTime,
		    ApproveTime,
		    ApproverId,
		    Approver,
		    EditorId,
		    Editor,
		    EditTime,
		    IsEnable,
		    FenceAlarmType,
		    CoordinateList,
		    Remark
        FROM
            fence";



        #region 构造函数
        public FenceData(IDatabaseContext databaseContext)
        {
            this.database = databaseContext;
        }
        #endregion

        #region InsertAsync
        /// <summary>
        /// 添加新电子围栏纪录
        /// </summary>
        /// <param name="fenceEntity"></param>
        /// <returns></returns>
        public async Task<FenceEntity> InsertAsync(FenceEntity entity)
        {
            return await this.database.Master.InsertAsync(entity);
        }
        #endregion

        #region updateAsync
        /// <summary>
        /// 修改电子围栏记录
        /// </summary>
        /// <param name="fenceEntity"></param>
        /// <returns></returns>
        public async Task<FenceEntity> UpdateAsync(FenceEntity entity)
        {
            return await this.database.Master.UpdateAsync(entity);
        }
        #endregion 

        #region Delete
        /// <summary>
        /// 删除电子围栏记录
        /// </summary>
        /// <param name="fenceId"></param>
        /// <returns></returns>
        public async Task<bool> DeleteAsync(long fenceId)
        {
            string sql = "DELETE FROM fence WHERE FenceId = ?fenceId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?FenceId", fenceId, DbType.Int64);
            return await this.database.Master.ExecuteAsync(sql, parameters, database.Transaction) > 0;
        }
        #endregion

        #region GetById
        /// <summary>
        /// 获取电子围栏记录
        /// </summary>
        /// <param name="fenceId"></param>
        /// <returns></returns>
        public async Task<FenceEntity> GetEntityAsync(long fenceId)
        {
            string sql = QUERY + " WHERE  FenceId = ?fenceId";

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?FenceId", fenceId, DbType.Int64);
            return await this.database.Master.QueryFirstOrDefaultAsync<FenceEntity>(sql, parameters);
        }
        #endregion

        #region GetListAsync 查询电子围栏记录
        public async Task<(IEnumerable<FenceEntity>, int)> GetListAsync(int? companyId, string workingCompanyName, string provinceCode, string cityCode, string countyCode, string fenceName, bool? isEnable, int pageIndex, int pageSize)
        {

            DynamicParameters parameters = new DynamicParameters();
            //查询条件
            List<string> wheres = new List<string>();

            if (companyId.HasValue)
            {
                wheres.Add("CtrlCompanyId=?CtrlCompanyId");
                parameters.Add("?CtrlCompanyId", companyId.Value, DbType.Int32);
            }
            if (!string.IsNullOrWhiteSpace(workingCompanyName))
            {
                wheres.Add("WorkCompanyName LIKE ?WorkCompanyName");
                parameters.Add("?WorkCompanyName", $"%{workingCompanyName}%", DbType.AnsiString);
            }

            if (!string.IsNullOrWhiteSpace(fenceName))
            {
                wheres.Add("FenceName LIKE ?FenceName");
                parameters.Add("?FenceName", $"%{fenceName}%", DbType.AnsiString);
            }
            if (isEnable.HasValue)
            {
                wheres.Add("IsEnable=?IsEnable");
                parameters.Add("?IsEnable", isEnable, DbType.Boolean);
            }

            if (!string.IsNullOrWhiteSpace(provinceCode))
            {
                wheres.Add("ProvinceCode =?ProvinceCode");
                parameters.Add("?ProvinceCode", provinceCode.Trim(), DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(cityCode))
            {
                wheres.Add("CityCode =?CityCode");
                parameters.Add("?CityCode", cityCode.Trim(), DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(countyCode))
            {
                wheres.Add("CountyCode =?CountyCode");
                parameters.Add("?CountyCode", countyCode.Trim(), DbType.AnsiString);
            }



            string sql = string.Format(@"{0} {1} LIMIT {2},{3} ; SELECT COUNT(1) FROM  fence  {1}",
                   QUERY,
                  wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                  (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
                  pageSize
                  );

            return await this.database.QueryMultipleAsync<FenceEntity, int>(sql, parameters);
        }
        #endregion


        public async Task<IEnumerable<FenceEntity>> GetOnlyListAsync(int? companyId, string workingCompanyName, string provinceCode, string cityCode, string countyCode, string fenceName, bool? isEnable)
        {
            DynamicParameters parameters = new DynamicParameters();
            //查询条件
            List<string> wheres = new List<string>();

            if (companyId.HasValue)
            {
                wheres.Add("CtrlCompanyId=?CtrlCompanyId");
                parameters.Add("?CtrlCompanyId", companyId.Value, DbType.Int32);
            }
            if (!string.IsNullOrWhiteSpace(workingCompanyName))
            {
                wheres.Add("WorkCompanyName LIKE ?WorkCompanyName");
                parameters.Add("?WorkCompanyName", $"%{workingCompanyName}%", DbType.AnsiString);
            }

            if (!string.IsNullOrWhiteSpace(fenceName))
            {
                wheres.Add("FenceName LIKE ?FenceName");
                parameters.Add("?FenceName", $"%{fenceName}%", DbType.AnsiString);
            }
            if (isEnable.HasValue)
            {
                wheres.Add("IsEnable=?IsEnable");
                parameters.Add("?IsEnable", isEnable, DbType.Boolean);
            }

            if (!string.IsNullOrWhiteSpace(provinceCode))
            {
                wheres.Add("ProvinceCode =?ProvinceCode");
                parameters.Add("?ProvinceCode", provinceCode.Trim(), DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(cityCode))
            {
                wheres.Add("CityCode =?CityCode");
                parameters.Add("?CityCode", cityCode.Trim(), DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(countyCode))
            {
                wheres.Add("CountyCode =?CountyCode");
                parameters.Add("?CountyCode", countyCode.Trim(), DbType.AnsiString);
            }


            string sql = string.Format(@"{0} {1}",
                   QUERY,
                  wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "");


            return await database.Slave.QueryAsync<FenceEntity>(sql, parameters);
        }

        #region GetByMongoId
        public async Task<FenceEntity> GetByMongoIdAsync(string mongoId)
        {
            string sql = QUERY + " WHERE  MongoId = ?mongoId";

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?MongoId", mongoId, DbType.String);
            return await this.database.Master.QueryFirstOrDefaultAsync<FenceEntity>(sql, parameters);
        }
        #endregion


        #region 根据劳务公司和区县编码获取围栏信息
        public async Task<FenceEntity> GetByWorkCompanyIdAndCountyCodeAsync(long workingCompanyId, string countyCode)
        {
            string sql = string.Format("{0} WHERE  WorkCompanyId = ?WorkCompanyId AND CountyCode=?CountyCode", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?WorkCompanyId", workingCompanyId, DbType.Int64);
            parameters.Add("?CountyCode", countyCode, DbType.AnsiString, size: 30);
            return await this.database.Master.QueryFirstOrDefaultAsync<FenceEntity>(sql, parameters);
        }
        #endregion

    }
}




